package cn.b.sky.form.service.impl;

import cn.b.sky.common.exception.ImeException;
import cn.b.sky.form.constans.CustomerPageConstans;
import cn.b.sky.form.dao.BusinessFieldDao;
import cn.b.sky.form.dao.BusinessTableDao;
import cn.b.sky.form.dao.BusinessTypeDao;
import cn.b.sky.form.enums.TableTypeEnum;
import cn.b.sky.form.model.BusinessDataTypeDef;
import cn.b.sky.form.model.BusinessField;
import cn.b.sky.form.model.BusinessType;
import cn.b.sky.form.model.condition.BusinessFieldCondition;
import cn.b.sky.form.service.BusinessTableService;
import cn.b.sky.form.utils.BusinessTypeDefinitionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * 
 * <strong>Title : BusinessTableServiceImpl.java<br></strong>
 * <strong>Package : cn.b.sky.flowable.service.impl<br></strong>
 * <strong>Description : </strong>业务表操作实现类<br> 
 * <strong>Create on : 2016年9月27日 下午3:49:12<br></strong>
 * <p>
 * @author dapeng.li<br>
 * @version <strong>v1.0.0</strong><br>
 * <br>
 * <strong>修改历史:</strong><br>
 * 修改人|修改日期|修改描述<br>
 * -------------------------------------------<br>
 * <br>
 * <br>
 */
@Service("businessTableService")
public class BusinessTableServiceImpl implements BusinessTableService {

  @Resource
  BusinessTypeDao businessTypeDao;

  @Resource
  BusinessFieldDao businessFieldDao;

  @Resource
  BusinessTableDao businessTableDao;

  @Resource
  SqlSessionFactory sqlSessionFactory;

  @Override
  public boolean doCreateOrUpdateBusinessTable(TableTypeEnum tableType, String siteCode, String businessType) {
    //查询业务类型
    BusinessType businessTypeInfo = businessTypeDao.getBusinessTypeByPrimaryKey(siteCode, businessType);
    if (businessTypeInfo == null) {
      throw ImeException.newException("businessTypeNotExist.M");
    }
    //查询业务类型字段
    BusinessFieldCondition businessFieldCondition = new BusinessFieldCondition();
    businessFieldCondition.setSiteCode(siteCode);
    businessFieldCondition.setBusinessType(businessType);
    List<BusinessField> fields = businessFieldDao.selectBusinessFieldsByCondition(businessFieldCondition);
    if (fields == null || fields.size() == 0) {

    }
    if (businessTableDao.getTableIsExist(businessTypeInfo.getDbTable()) == 0) {
      //数据库中不存该表，则创建表
      String sql = getCreateTableSql(tableType, businessTypeInfo, fields);
      executeSql(sql);
    } else {
      //数据库中已经存在，则更新表
      String sql = updateTable(tableType, businessTypeInfo, fields);
      if (!StringUtils.isBlank(sql)) {
        executeSql(sql);
      }
    }
    return true;
  }

  /**
   * 
   * <strong>Title : executeSql<br></strong>
   * <strong>Description : </strong>执行SQL<br> 
   * <strong>Create on : 2016年9月29日 下午5:26:27<br></strong>
   * <p>
   * @param sql
   * void
   * @throws 
   * @author dapeng.li<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  void executeSql(String sql) {
    String[] sqlList = sql.split(";");
    for (String s : sqlList) {
      businessTableDao.executeSql(s);
    }
  }

  /**
   * 
   * <strong>Title : updateTable<br></strong>
   * <strong>Description : </strong>获得更新表语句<br> 
   * <strong>Create on : 2016年9月29日 下午5:28:06<br></strong>
   * <p>
   * @param tableType
   * @param businessTypeInfo
   * @param fields
   * @param es
   * @return
   * String
   * @throws 
   * @author dapeng.li<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  String updateTable(TableTypeEnum tableType, BusinessType businessTypeInfo, List<BusinessField> fields) {
    String updateSql = "";
    String commentSql = "";
    List<String> columns = businessTableDao.getTableColumns(businessTypeInfo.getDbTable());
    for (String column : columns) {
      BusinessField b = new BusinessField();
      b.setDbField(column);
      if (!fields.contains(b)) {
        //业务字段中不存在的字段则进行删除
        updateSql += "alter table " + businessTypeInfo.getDbTable() + " drop column " + column + ";";
      }
    }
    for (BusinessField field : fields) {
      if (!StringUtils.isBlank(field.getDbField())) {
        if (!columns.contains(field.getDbField().toUpperCase())) {
          //数据库中存在的业务字段 则进行添加
          updateSql += "alter table " + businessTypeInfo.getDbTable() + " add " + field.getDbField() + " "
              + getDataType(field.getDataType(), tableType) + " " + getNullAbledSql(field);
          if (tableType.equals(TableTypeEnum.MYSQL)) {
            updateSql += " COMMENT '" + field.getFieldLabel() + "'";
          }
          updateSql += ";";
          if (tableType.equals(TableTypeEnum.ORACLE)) {
            commentSql = getOracleComment(businessTypeInfo, commentSql, field);
          }
        }
      }
    }
    updateSql += commentSql;
    return updateSql;
  }

  /**
   * 
   * <strong>Title : getOracleComment<br></strong>
   * <strong>Description : </strong>获得ORACLE注释<br> 
   * <strong>Create on : 2016年9月29日 下午5:29:37<br></strong>
   * <p>
   * @param businessTypeInfo
   * @param commentSql
   * @param field
   * @return
   * String
   * @throws 
   * @author dapeng.li<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  private String getOracleComment(BusinessType businessTypeInfo, String commentSql, BusinessField field) {
    commentSql += "\n";
    commentSql += "comment on column " + businessTypeInfo.getDbTable() + "." + field.getDbField() + " is '"
        + field.getFieldLabel() + "';";
    return commentSql;
  }

  /**
   * 
   * <strong>Title : getCreateTableSql<br></strong>
   * <strong>Description : </strong>获得表创建语句<br> 
   * <strong>Create on : 2016年9月29日 下午2:08:15<br></strong>
   * <p>
   * @param tableType
   * @param businessTypeInfo
   * @param fields
   * @param es
   * @return
   * String
   * @throws 
   * @author dapeng.li<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  String getCreateTableSql(TableTypeEnum tableType, BusinessType businessTypeInfo, List<BusinessField> fields) {
    String filedSql = "";
    String commentSql = "";
    if (tableType.equals(TableTypeEnum.ORACLE)) {
      commentSql += ";" + "\n" + "comment on table " + businessTypeInfo.getDbTable() + " is '"
          + businessTypeInfo.getBusinessTypeText() + "';";
    } else if (tableType.equals(TableTypeEnum.MYSQL)) {
      commentSql += "\n" + "comment='" + businessTypeInfo.getBusinessTypeText() + "';";
    }
    int dbFieldCount = 0;
    boolean isHaveId = false;
    boolean isHaveSitecode = false;
    boolean isHaveBusinessCode = false;
    for (BusinessField field : fields) {
      if (!StringUtils.isBlank(field.getDbField())) {
        if (field.getDbField().toUpperCase().equals(CustomerPageConstans.SITECODE)) {
          isHaveSitecode = true;
        } else if (field.getDbField().toUpperCase().equals(CustomerPageConstans.ID)) {
          isHaveId = true;
        } else if (field.getDbField().toUpperCase().equals(CustomerPageConstans.BUSINESSCODE)) {
          isHaveBusinessCode = true;
        }
        dbFieldCount++;
        filedSql += field.getDbField() + " " + getDataType(field.getDataType(), tableType) + " "
            + getNullAbledSql(field);
        if (tableType.equals(TableTypeEnum.MYSQL)) {
          filedSql += " COMMENT '" + field.getFieldLabel() + "'";
        }
        filedSql += ",";
        if (tableType.equals(TableTypeEnum.ORACLE)) {
          commentSql = getOracleComment(businessTypeInfo, commentSql, field);

        }
      }
    }
    if (dbFieldCount == 0) {
      throw ImeException.newException("noDbField.M");
    }
    if (!(isHaveId && isHaveSitecode && isHaveBusinessCode)) {
      throw ImeException.newException("dbKeyNotExist.M");
    }
    String keySql = "CONSTRAINT " + businessTypeInfo.getDbTable() + "_PK primary key (" + CustomerPageConstans.ID
        + "),";
    String uniqueSql = "CONSTRAINT " + businessTypeInfo.getDbTable() + "_AK UNIQUE (" + CustomerPageConstans.SITECODE
        + "," + CustomerPageConstans.BUSINESSCODE + ")";
    String sql = "create table " + businessTypeInfo.getDbTable() + "(" + filedSql + keySql + uniqueSql + ")"
        + commentSql;
    return sql;
  }

  /**
   * 
   * <strong>Title : getDataType<br></strong>
   * <strong>Description : </strong>获得数据类型<br> 
   * <strong>Create on : 2016年9月29日 下午5:30:10<br></strong>
   * <p>
   * @param dataType
   * @param elements
   * @param tableType
   * @return
   * String
   * @throws 
   * @author dapeng.li<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  String getDataType(String dataType, TableTypeEnum tableType) {
    BusinessDataTypeDef businessDataTypeDef = BusinessTypeDefinitionUtils.getDataType(dataType);
    if (businessDataTypeDef == null) {
      throw ImeException.newException("dataTypeNotExist.M");
    }
    if (tableType.equals(TableTypeEnum.ORACLE)) {
      return businessDataTypeDef.getOracleType();
    } else if (tableType.equals(TableTypeEnum.MYSQL)) {
      return businessDataTypeDef.getMysqlType();
    } else {
      throw ImeException.newException("dataTypeNotExist.M");
    }

  }

  /**
   * 
   * <strong>Title : getNullAbledSql<br></strong>
   * <strong>Description : </strong>获取是否允许空值<br> 
   * <strong>Create on : 2016年9月30日 上午11:05:22<br></strong>
   * <p>
   * @param field
   * @return
   * String
   * @throws 
   * @author dapeng.li<br>
   * @version <strong>v1.0.0</strong><br>
   * <br>
   * <strong>修改历史:</strong><br>
   * 修改人 | 修改日期 | 修改描述<br>
   * -------------------------------------------<br>
   * <br>
   * <br>
   */
  String getNullAbledSql(BusinessField field) {
    if (field.getSystemFieldFlag() == 1 && field.getRequiredFlag() == 1) {
      return "not null";
    } else {
      return "";
    }
  }

  @Override
  public int getTableIsExist(String siteCode, String businessType) {
    BusinessType type = businessTypeDao.getBusinessTypeByPrimaryKey(siteCode, businessType);
    if (type != null) {
      return businessTableDao.getTableIsExist(type.getDbTable());
    } else {
      return 0;
    }
  }

}
